﻿using DataAccess.DB;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.ComponentModel.DataAnnotations;
using System.Data;
using DataAccess.DB;

namespace ImportData
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            if (file.ShowDialog() == DialogResult.OK)
            {
                textBox_File.Text = file.FileName;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            BackgroundWorker backgroundWorker = new BackgroundWorker();
            backgroundWorker.WorkerSupportsCancellation = true;
            backgroundWorker.DoWork += new DoWorkEventHandler(this.ImportData);
            backgroundWorker.RunWorkerAsync();
            if (backgroundWorker.IsBusy)
            {
                backgroundWorker.CancelAsync();
            }
        }

        public decimal ConvertCurrencyToMoney(string money)
        {
            if (string.IsNullOrEmpty(money))
            {
                money = "0";
            }
            return (Convert.ToDecimal(money.Replace(",", "")));
        }

        public string ConvertDecimaToCurrency(decimal money)
        {
            return string.Format("{0:C}", money).Replace("$", "");
        }

        private void ImportData(object sender, DoWorkEventArgs e)
        {
            var db = new db_YTeTBEntities();
            try
            {

                this.Invoke((MethodInvoker)delegate
                {
                    pictureBox.Visible = true;
                    btnImport.Enabled = false;
                });
                if (!string.IsNullOrEmpty(textBox_File.Text))
                {

                    var table = DataMigrate(textBox_File.Text.Trim(), textBoxShetname.Text.Trim());
                    var count = table.Rows.Count;


                    if (table.Rows.Count > 0 && table != null)
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            DataRow row = table.Rows[i];
                            var sp = new SanPham();
                            var DVT = Convert.ToString(row["ĐVT"]);
                            var dgkm = row["Diễn giải khi mua"];
                            sp.MaSP = Convert.ToString(row["Mã hàng (*)"]);
                            sp.TenSP = Convert.ToString(row["Tên hàng (*)"]);
                            if (string.IsNullOrEmpty(DVT))
                            {
                                DVT = string.Empty;
                            }
                            if (dgkm == null)
                            {
                                dgkm = string.Empty;
                            }
                            sp.DonVi = Convert.ToString(DVT);
                            //sp.ID_KhoMD = 4;
                            sp.GiaNhap = ConvertDecimaToCurrency(Convert.ToDecimal(row["Đơn giá mua"]));
                            sp.GiaBan = ConvertDecimaToCurrency(Convert.ToDecimal(row["Đơn giá bán 1"]));

                            sp.Description = Convert.ToString(row["Diễn giải khi mua"]);
                            //sp.SoLuong = Convert.ToInt32(row["Số lượng tồn tối thiểu"]);
                            db.SanPhams.Add(sp);
                            db.SaveChanges();

                        }
                    }
                    MessageBox.Show("xong san pham");
                }
                if (!string.IsNullOrEmpty(textBoxDVCCKH.Text))
                {
                    var table = DataMigrate(textBoxDVCCKH.Text.Trim(), textBoxShetname.Text.Trim());
                    var count = table.Rows.Count;


                    if (table.Rows.Count > 0 && table != null)
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            DataRow row = table.Rows[i];
                            var dvcc = new DVCC();
                            var kh = new KhachHang();
                            var Ma_KHDVCC = Convert.ToString(row["Mã KH, NCC"]);
                            var Ten_KHDVCC = Convert.ToString(row["Tên KH, NCC"]);
                            var DiaChi_KH_DVCC = Convert.ToString(row["Địa chỉ"]);
                            var DT_KH_DVCC = Convert.ToString(row["Điện thoại"]);
                            var MaSoThue_KH_DVCC = Convert.ToString(row["Mã số thuế"]);
                           
                            dvcc.MaDVCC = Ma_KHDVCC;
                            kh.MaKH = Ma_KHDVCC;

                            dvcc.TenDVCC = Ten_KHDVCC;
                            kh.TenKH = Ten_KHDVCC;

                            dvcc.DiaChi = DiaChi_KH_DVCC;
                            kh.DiaChiKH = DiaChi_KH_DVCC;

                            dvcc.MaSoThue = MaSoThue_KH_DVCC;
                            kh.MaSoThue = MaSoThue_KH_DVCC;

                            dvcc.TongNo = "0.00";
                            kh.TongNo = "0.00";

                            dvcc.SDT = DT_KH_DVCC;
                            kh.SDT = DT_KH_DVCC;
                            db.KhachHangs.Add(kh);
                            db.DVCCs.Add(dvcc);
                            db.SaveChanges();
                            //var sp = new SanPham();
                            //var DVT = Convert.ToString(row["ĐVT"]);
                            //var dgkm = row["Diễn giải khi mua"];
                            //sp.MaSP = Convert.ToString(row["Mã hàng (*)"]);
                            //sp.TenSP = Convert.ToString(row["Tên hàng (*)"]);
                            //if (string.IsNullOrEmpty(DVT))
                            //{
                            //    DVT = string.Empty;
                            //}
                            //if (dgkm == null)
                            //{
                            //    dgkm = string.Empty;
                            //}
                            //sp.DonVi = Convert.ToString(DVT);
                            //sp.ID_KhoMD = 4;
                            //sp.GiaNhap = ConvertDecimaToCurrency(Convert.ToDecimal(row["Đơn giá mua"]));
                            //sp.GiaBan = ConvertDecimaToCurrency(Convert.ToDecimal(row["Đơn giá bán 1"]));

                            //sp.Description = Convert.ToString(row["Diễn giải khi mua"]);
                            //sp.SoLuong = Convert.ToInt32(row["Số lượng tồn tối thiểu"]);
                           

                        }

                    }
                    MessageBox.Show("xong KH-DVCC");
                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            this.Invoke((MethodInvoker)delegate
            {
                pictureBox.Visible = false;
                btnImport.Enabled = true;
            });
        }


        public static DataTable DataMigrate(string filename, string sheet)
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            OleDbConnection con = new OleDbConnection(strConn);
            string comand = @"Select * from [" + sheet + "$]";
            OleDbDataAdapter adap = new OleDbDataAdapter(comand, con);
            DataTable table = new DataTable("Data");
            adap.Fill(table);
            return table;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            if (file.ShowDialog() == DialogResult.OK)
            {
                textBoxDVCCKH.Text = file.FileName;
            }
        }
    }
}
